import { Inject, Provide } from '@midwayjs/core';
import { query, transaction } from '../dbConnPool/mariadb'; // 引入查询函数
import { PatientDTO, UpdatePatientDTO } from '../dto/patient';
import { PageDTO, SearchDTO } from '../dto/page';
import { Context } from '@midwayjs/koa';
import { BigIntService } from '../service/bigInt.service';
import { uuidv7 } from 'uuidv7';
import { Caching } from '@midwayjs/cache-manager';
import { CachingFactory, MidwayCache } from '@midwayjs/cache-manager';
import { InjectClient } from '@midwayjs/core';
@Provide()
export class PatientDao {
  @Inject()
  ctx: Context;
  @Inject()
  bigIntService: BigIntService;
  //查询患者是否存在
  async findPatient(departmentId: string, name: string, phone: string) {
    const sql = `
        SELECT
            COUNT(*) AS count
        FROM 
            patient
        WHERE 
            department_id = ? AND name = ? AND phone = ? AND valid_flag = 1
        `;
    const sqlParams = [departmentId, name, phone];
    const result = await query(sql, sqlParams);
    return result[0].count;
  }
  //通过患者id查询患者是否存在
  async findPatientById(patientId: string) {
    const sql = `
        SELECT
            COUNT(*) AS count
        FROM 
            patient
        WHERE 
            id = ? 
        `;
    const sqlParams = [patientId];
    const result = await query(sql, sqlParams);
    return result[0].count;
  }
  //查询患者id
  async getPatientId(name: string, phone: string) {
    const sql = `
        SELECT
            id
        FROM 
            patient
        WHERE 
            name = ? AND phone = ? AND valid_flag = 1
        `;
    const sqlParams = [name, phone];
    const result = await query(sql, sqlParams, false);
    return result[0].id;
  }
  //创建患者信息
  @InjectClient(CachingFactory, 'redis')
  cache: MidwayCache; //redis缓存
  async createPatient(patientDto: PatientDTO) {
    const userId = this.ctx.state.user.userId;
    const departmentId = this.ctx.state.user.departmentId;
    const patientId = uuidv7();
    return transaction(async conn => {
      const sql = `
        INSERT INTO patient (id,department_id, user_id,visit_card_id, name, sex, age, phone, created_at)
        VALUES (?,?,?,?,?,?,?,?,NOW())
        `;
      const sqlParams = [
        patientId,
        departmentId,
        userId,
        patientDto.visitCardId,
        patientDto.name,
        patientDto.sex,
        patientDto.age,
        patientDto.phone,
      ];
      await query(sql, sqlParams, false, conn);
      const sql2 = `
        INSERT INTO daily_statistics (date, new_patients)
            VALUES (CURDATE(), 1)
            ON DUPLICATE KEY UPDATE new_patients = new_patients + 1;
            `;
      await query(sql2, [], false, conn);
      const sql3 = `
            INSERT INTO department_daily_statistics (department_id, date, new_patients)
                VALUES (?,CURDATE(), 1)
                ON DUPLICATE KEY UPDATE new_patients = new_patients + 1;`;
      await query(sql3, [departmentId], false, conn);
      await this.cache.del(`departmentPatient:${departmentId}`);
      return patientId;
    });
  }
  //用户获取我的患者信息
  @Caching('redis', ctx => {
    if (
      ctx.methodArgs.length > 0 &&
      ctx.methodArgs[1].pageNum == 1 &&
      ctx.methodArgs[1].pageSize == 10 &&
      ctx.methodArgs[1].type == 1
    ) {
      return `departmentPatient:${ctx.methodArgs[0]}`;
    }
    return null;
  })
  async getMyPatient(departmentId: string, pageDto: PageDTO) {
    const { pageNum, pageSize, type } = pageDto;
    return transaction(async conn => {
      const sql = `
        SELECT
            p.id AS patientId,p.department_id AS departmentId,p.visit_card_id AS visitCardId,u.name AS creatorName, p.name, s.item_label AS sex, p.age, p.phone, p.created_at AS createdAt, p.last_visit_date AS lastVisitDate, p.valid_flag AS validFlag
        FROM 
            patient p
        LEFT JOIN
            user u ON p.user_id = u.user_id
        LEFT JOIN
            sys_dict_item s ON p.sex = s.item_code AND s.dict_code = 'SEX'    
        WHERE 
            p.department_id = ? AND p.valid_flag = ?
        ORDER BY
            p.last_visit_date DESC
        LIMIT ?, ?
        `;
      const sqlParams = [
        departmentId,
        type,
        (pageNum - 1) * pageSize,
        pageSize,
      ];
      const list = await query(sql, sqlParams, false, conn);
      const sql1 = `
        SELECT
        COUNT(*) AS total
        FROM 
        patient
        WHERE 
        department_id = ? AND valid_flag = ?
        `;
      const sqlParams1 = [departmentId, type];
      const total = await query(sql1, sqlParams1, false, conn);
      return this.bigIntService.bigInt({ total: total[0].total, list });
    });
  }
  // 患者搜索功能
  async searchPatients(departmentId: string, searchDto: SearchDTO) {
    const { keyword, pageNum, pageSize, type } = searchDto;
    return transaction(async conn => {
      let sql = `
    SELECT
        p.id AS patientId,p.department_id AS departmentId,p.visit_card_id AS visitCardId,u.name AS creatorName, p.name, s.item_label AS sex, p.age, p.phone, p.created_at AS createdAt, p.last_visit_date AS lastVisitDate, p.valid_flag AS validFlag
    FROM 
        patient p
    LEFT JOIN
        user u ON p.user_id = u.user_id
    LEFT JOIN
        sys_dict_item s ON p.sex = s.item_code AND s.dict_code = 'SEX'    
    WHERE 
        p.department_id = ? AND p.valid_flag = ?`;
      sql += ' AND (p.name LIKE ? OR p.phone LIKE ? OR p.visit_card_id LIKE ?)';
      sql += `
    ORDER BY
        p.created_at DESC
    LIMIT ?, ?`;

      const sqlParams = [
        departmentId,
        type,
        `%${keyword}%`,
        `%${keyword}%`,
        `%${keyword}%`,
        (pageNum - 1) * pageSize,
        pageSize,
      ];
      const list = await query(sql, sqlParams, false, conn);

      // 查询总记录数
      let sql1 = `
    SELECT
        COUNT(*) AS total
    FROM 
        patient
    WHERE 
        department_id = ? AND valid_flag = ?`;
      sql1 += ' AND (name LIKE ? OR phone LIKE ? OR visit_card_id LIKE ?)';

      const sqlParams1 = [
        departmentId,
        type,
        `%${keyword}%`,
        `%${keyword}%`,
        `%${keyword}%`,
      ];
      const total = await query(sql1, sqlParams1, false, conn);

      return this.bigIntService.bigInt({ total: total[0].total, list });
    });
  }
  //管理员获取所有患者信息
  async getAllPatient(pageDto: PageDTO) {
    const { pageNum, pageSize, type } = pageDto;
    return transaction(async conn => {
      const sql = `
        SELECT
            p.id AS patientId,p.department_id AS departmentId,p.visit_card_id AS visitCardId, d.name AS departmentName, u.name AS creatorName, p.name, s.item_label AS sex, p.age, p.phone, p.created_at AS createdAt, p.last_visit_date AS lastVisitDate, p.valid_flag AS validFlag
        FROM 
            patient p
        LEFT JOIN
            user u ON p.user_id = u.user_id
        LEFT JOIN
            department d ON p.department_id = d.id
        LEFT JOIN
            sys_dict_item s ON p.sex = s.item_code AND s.dict_code = 'SEX'    
        WHERE 
            p.valid_flag = ?
        ORDER BY
            p.created_at DESC
        LIMIT ?, ?
        `;
      const sqlParams = [type, (pageNum - 1) * pageSize, pageSize];
      const list = await query(sql, sqlParams, false, conn);
      const sql1 = `
        SELECT
        COUNT(*) AS total
        FROM 
        patient
        WHERE 
        valid_flag = ?
        `;
      const sqlParams1 = [type];
      const total = await query(sql1, sqlParams1, false, conn);
      return { total: total[0].total, list };
    });
  }
  //修改患者信息
  async updatePatientInfo(updatePatientDto: UpdatePatientDTO) {
    const userId = this.ctx.state.user.userId;
    const setClause = [];
    const sqlParams = [];
    sqlParams.push(userId);
    for (const [key, value] of Object.entries(updatePatientDto)) {
      if (key === 'patientId') {
        continue;
      }
      if (key === 'departmentId') {
        continue;
      }
      const snakeKey = this.camelToSnake(key);
      setClause.push(`${snakeKey} = ?`);
      sqlParams.push(value);
    }
    sqlParams.push(updatePatientDto.patientId);
    const sql = `
        UPDATE patient
        SET user_id = ?, ${setClause.join(', ')}
        WHERE id = ? 
        `;
    await query(sql, sqlParams);
    await this.cache.del(`departmentPatient:${updatePatientDto.departmentId}`);
    return true;
  }
  // 工具函数：驼峰命名转下划线命名
  camelToSnake(str: string): string {
    return str.replace(/([a-z0-9])([A-Z])/g, '$1_$2').toLowerCase();
  }
}
